// Copyright (c) Appeon. All rights reserved.
// Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. 
// You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0.

using System.Collections.Generic;
using System.Data;
using JetBrains.Annotations;
using System.Threading;
using System.Threading.Tasks;
using System.Data.Common;
using System;

namespace SnapObjects.Data
{
    public abstract class AdoDbProvider : IAdoDbProvider
    {
        protected readonly DataContext _dataContext;

        protected AdoDbProvider(DataContext dataContext)
        {
            _dataContext = dataContext;
        }

        public IAdoDbResultSet<TModel> ExecuteSqlQuery<TModel>(
            string sqlText,
            IEnumerable<IAdoDbParameter> parameters,
            CommandBehavior commandBehavior = CommandBehavior.Default)
        {
            var command = (AdoDbCommand)this.CreateAdoDbCommand(sqlText, parameters);

            var result = command.ExecuteQuery(_dataContext, commandBehavior);

            return this.CreateResultSet<TModel>(result.Item1, result.Item2, parameters);
        }

        public Task<IAdoDbResultSet<TModel>> ExecuteSqlQueryAsync<TModel>(
            string sqlText,
            IEnumerable<IAdoDbParameter> parameters,
            CancellationToken cancellationToken = default)
        {
            return this.ExecuteSqlQueryAsync<TModel>(
                sqlText, parameters, CommandBehavior.Default, cancellationToken);
        }

        public async Task<IAdoDbResultSet<TModel>> ExecuteSqlQueryAsync<TModel>(
            string sqlText,
            IEnumerable<IAdoDbParameter> parameters,
            CommandBehavior commandBehavior,
            CancellationToken cancellationToken = default)
        {
            var command = (AdoDbCommand)this.CreateAdoDbCommand(sqlText, parameters);

            var result = await command.ExecuteQueryAsync(_dataContext, commandBehavior, cancellationToken);

            return this.CreateResultSet<TModel>(result.Item1, result.Item2, parameters);
        }

        public IAdoDbDataVisitor ExecuteSqlQuery(
            string sqlText,
            IEnumerable<IAdoDbParameter> parameters,
            CommandBehavior commandBehavior = CommandBehavior.Default)
        {
            var command = (AdoDbCommand)this.CreateAdoDbCommand(sqlText, parameters);

            var result = command.ExecuteQuery(_dataContext, commandBehavior);

            return this.CreateDataVisitor(result.Item1, result.Item2, parameters);
        }

        public Task<IAdoDbDataVisitor> ExecuteSqlQueryAsync(
            string sqlText,
            IEnumerable<IAdoDbParameter> parameters,
            CancellationToken cancellationToken = default)
        {
            return this.ExecuteSqlQueryAsync(sqlText, parameters, CommandBehavior.Default, cancellationToken);
        }

        public async Task<IAdoDbDataVisitor> ExecuteSqlQueryAsync(
            string sqlText,
            IEnumerable<IAdoDbParameter> parameters,
            CommandBehavior commandBehavior,
            CancellationToken cancellationToken = default)
        {
            var command = (AdoDbCommand)this.CreateAdoDbCommand(sqlText, parameters);

            var result = await command.ExecuteQueryAsync(_dataContext, commandBehavior, cancellationToken);

            return this.CreateDataVisitor(result.Item1, result.Item2, parameters);
        }

        public IAdoDbDataVisitor ExecuteSqlProcedure(
            string sqlText,
            IEnumerable<IAdoDbParameter> parameters,
            CommandBehavior commandBehavior = CommandBehavior.Default)
        {
            sqlText = this.GenerateSqlProcedureLiteral(sqlText, ref parameters);

            var command = (AdoDbCommand)this.CreateAdoDbCommand(sqlText, parameters, CommandType.StoredProcedure);

            var result = command.ExecuteQuery(_dataContext, commandBehavior);

            return this.CreateDataVisitor(result.Item1, result.Item2, parameters);
        }

        public Task<IAdoDbDataVisitor> ExecuteSqlProcedureAsync(
            string sqlText,
            IEnumerable<IAdoDbParameter> parameters,
            CancellationToken cancellationToken = default)
        {
            return this.ExecuteSqlProcedureAsync(sqlText, parameters, CommandBehavior.Default, cancellationToken);
        }

        public async Task<IAdoDbDataVisitor> ExecuteSqlProcedureAsync(
            string sqlText,
            IEnumerable<IAdoDbParameter> parameters,
            CommandBehavior commandBehavior,
            CancellationToken cancellationToken = default)
        {
            sqlText = this.GenerateSqlProcedureLiteral(sqlText, ref parameters);

            var command = (AdoDbCommand)this.CreateAdoDbCommand(sqlText, parameters, CommandType.StoredProcedure);

            var result = await command.ExecuteQueryAsync(_dataContext, commandBehavior, cancellationToken);

            return this.CreateDataVisitor(result.Item1, result.Item2, parameters);
        }

        public virtual string GenerateSqlProcedureLiteral(string sqlText,
            ref IEnumerable<IAdoDbParameter> parameters)
        {
            return sqlText;
        }

        public int ExecuteSqlCommand(string sqlText, IEnumerable<IAdoDbParameter> parameters)
        {
            var command = (AdoDbCommand)this.CreateAdoDbCommand(sqlText, parameters);

            var result = command.ExecuteNonQuery(_dataContext);

            return result;
        }

        public Task<int> ExecuteSqlCommandAsync(
            string sqlText,
            IEnumerable<IAdoDbParameter> parameters,
            CancellationToken cancellationToken = default)
        {
            var command = (AdoDbCommand)this.CreateAdoDbCommand(sqlText, parameters);

            return command.ExecuteNonQueryAsync(_dataContext, cancellationToken);
        }

        public IAdoDbResultSet<TModel> ExecuteSqlProcedure<TModel>(
            string sqlText,
            IEnumerable<IAdoDbParameter> parameters,
            CommandBehavior commandBehavior = CommandBehavior.Default)
        {
            sqlText = this.GenerateSqlProcedureLiteral(sqlText, ref parameters);

            var command = (AdoDbCommand)this.CreateAdoDbCommand(sqlText, parameters, CommandType.StoredProcedure);

            var result = command.ExecuteQuery(_dataContext, commandBehavior);

            return this.CreateResultSet<TModel>(result.Item1, result.Item2, parameters);
        }

        public Task<IAdoDbResultSet<TModel>> ExecuteSqlProcedureAsync<TModel>(
            string sqlText,
            IEnumerable<IAdoDbParameter> parameters,
            CancellationToken cancellationToken = default)
        {
            return this.ExecuteSqlProcedureAsync<TModel>(sqlText, parameters, CommandBehavior.Default, cancellationToken);
        }

        public async Task<IAdoDbResultSet<TModel>> ExecuteSqlProcedureAsync<TModel>(
            string sqlText,
            IEnumerable<IAdoDbParameter> parameters,
            CommandBehavior commandBehavior,
            CancellationToken cancellationToken = default)
        {
            sqlText = this.GenerateSqlProcedureLiteral(sqlText, ref parameters);

            var command = (AdoDbCommand)this.CreateAdoDbCommand(sqlText, parameters, CommandType.StoredProcedure);

            var result = await command.ExecuteQueryAsync(_dataContext, commandBehavior, cancellationToken);

            return this.CreateResultSet<TModel>(result.Item1, result.Item2, parameters);
        }

        public int ExecuteSqlProcedureCommand(string sqlText, IEnumerable<IAdoDbParameter> parameters)
        {
            sqlText = this.GenerateSqlProcedureLiteral(sqlText, ref parameters);

            var command = (AdoDbCommand)this.CreateAdoDbCommand(sqlText, parameters, CommandType.StoredProcedure);

            var result = command.ExecuteNonQuery(_dataContext);

            return result;
        }

        public Task<int> ExecuteSqlProcedureCommandAsync(
            string sqlText,
            IEnumerable<IAdoDbParameter> parameters,
            CancellationToken cancellationToken = default)
        {
            sqlText = this.GenerateSqlProcedureLiteral(sqlText, ref parameters);

            var command = (AdoDbCommand)this.CreateAdoDbCommand(sqlText, parameters, CommandType.StoredProcedure);

            return command.ExecuteNonQueryAsync(_dataContext, cancellationToken);
        }

        public TValue ExecuteSqlScalar<TValue>(string sqlText, IEnumerable<IAdoDbParameter> parameters)
        {
            var command = (AdoDbCommand)this.CreateAdoDbCommand(sqlText, parameters);

            var value = command.ExecuteScalar(_dataContext);

            if (ValueConvert.TryConvert(value, out TValue result))
            {
                return result;
            }

            return (TValue)value;
        }

        public async Task<TValue> ExecuteSqlScalarAsync<TValue>(
            string sqlText,
            IEnumerable<IAdoDbParameter> parameters,
            CancellationToken cancellationToken = default)
        {
            var command = (AdoDbCommand)this.CreateAdoDbCommand(sqlText, parameters);

            var value = await command.ExecuteScalarAsync(_dataContext, cancellationToken);

            if (ValueConvert.TryConvert(value, out TValue result))
            {
                return result;
            }

            return (TValue)value;
        }

        public void ExecuteSqlValidation(string sqlText, IEnumerable<IAdoDbParameter> parameters)
        {
            var command = (AdoDbCommand)this.CreateAdoDbCommand(sqlText, parameters);

            command.ExecuteParseOnly(_dataContext);
        }

        public Task ExecuteSqlValidationAsync(
            string sqlText, IEnumerable<IAdoDbParameter> parameters, CancellationToken cancellationToken = default)
        {
            var command = (AdoDbCommand)this.CreateAdoDbCommand(sqlText, parameters);

            return command.ExecuteParseOnlyAsync(_dataContext);
        }


        internal IAdoDbResultSet<TModel> ExecuteSqlQuery<TModel>(
            string sqlText,
            IEnumerable<IAdoDbParameter> parameters,
            bool addExceptionToTransaction,
            CommandBehavior commandBehavior = CommandBehavior.Default)
        {
            var command = (AdoDbCommand)this.CreateAdoDbCommand(sqlText, parameters, CommandType.Text, addExceptionToTransaction);

            var result = command.ExecuteQuery(_dataContext, commandBehavior);

            return this.CreateResultSet<TModel>(result.Item1, result.Item2, parameters);
        }

        internal async Task<IAdoDbResultSet<TModel>> ExecuteSqlQueryAsync<TModel>(
            string sqlText,
            IEnumerable<IAdoDbParameter> parameters,
            bool addExceptionToTransaction,
            CommandBehavior commandBehavior,
            CancellationToken cancellationToken = default)
        {
            var command = (AdoDbCommand)this.CreateAdoDbCommand(sqlText, parameters, CommandType.Text, addExceptionToTransaction);

            var result = await command.ExecuteQueryAsync(_dataContext, commandBehavior, cancellationToken);

            return this.CreateResultSet<TModel>(result.Item1, result.Item2, parameters);
        }

        protected abstract IAdoDbResultSet<TModel> CreateResultSet<TModel>(
            IDbCommand command, IDataReader reader, IEnumerable<IAdoDbParameter> parameters);

        protected abstract IAdoDbDataVisitor CreateDataVisitor(
            IDbCommand command, IDataReader reader, IEnumerable<IAdoDbParameter> parameters);

        protected abstract IAdoDbCommand CreateAdoDbCommand([NotNull] string sqlText,
           IEnumerable<IAdoDbParameter> parameters, CommandType commandType = CommandType.Text, bool addExceptionToTransaction = true);
    }
}
